﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using QuanLyKhachSan.BusinessObject;
using QuanLyKhachSan.DataAccess;

namespace QuanLyKhachSan.BusinessLogic
{
    class PhongBL
    {
        ConnectData connData = new ConnectData();

        //Dem phong trong
        public static int DemPhongTrong() {
            ConnectData connData1 = new ConnectData();
            SqlConnection conn = connData1.Connect1();
            conn.Open();
            SqlCommand objCmd = new SqlCommand("LayPhongTrong", conn);
            SqlDataReader dr = objCmd.ExecuteReader();
            int count = 0;
            while (dr.Read())
            {
                count++;
            }
            conn.Close();
            return count;
        }

        //Dem tong phong
        public static int DemPhong()
        {
            ConnectData connData1 = new ConnectData();
            SqlConnection conn = connData1.Connect1();
            conn.Open();
            SqlCommand objCmd = new SqlCommand("LayDSPhong", conn);
            SqlDataReader dr = objCmd.ExecuteReader();
            int count = 0;
            while (dr.Read())
            {
                count++;
            }
            conn.Close();
            return count;
        }
        //lấy danh sách loại phòng
        public DataTable LayDSPhong() {
            string sql = "LayDSPhong";
            return connData.GetDataTable(sql);
        }

        //lấy danh sach tình trạng phòng
        public DataTable LayTinhTrangPhong()
        {
            string sql = "select distinct TinhTrang,"
                + "TTPhong = case TinhTrang when 0 then N'Trống'"
                + "when 1 then N'Không Trống' end from Phong";
            return connData.GetDataTable(sql);
        }

        //Tim kiem
        public DataTable TimKiemPhong(string timKiem)
        {
            string sql = "select * from Phong where TenPhong like '%" + timKiem + "%' or MaPhong like '%" + timKiem + "%'";
            return connData.GetDataTable(sql);
        }

        //lấy danh sách thuê phòng trống
        public DataTable LayDSThuePhongTrong()
        {
            string sql = "LayPhongTrong";
            return connData.GetDataTable(sql);
        }
        //Lay ma loaij phong

        public static string LayMaLoaiPhong(string maPhong)
        {
            string maLoaiPhong = null;
            ConnectData connData1 = new ConnectData();
            SqlConnection conn = connData1.Connect1();
            conn.Open();
            SqlCommand objCmd = new SqlCommand("Select MaLoaiPhong from Phong where MaPhong='"+ maPhong+ "'", conn);
            SqlDataReader dr = objCmd.ExecuteReader();
            while (dr.Read())
            {
                maLoaiPhong = dr.GetValue(0).ToString();
            }
            conn.Close();
            return maLoaiPhong;
        }

        //thêm mới nhân viên
        public void ThemPhong(PhongBO p)
        {

            //string sql = string.Format("INSERT INTO Phong (MaPhong, TenPhong, SoDienThoai, MaLoaiPhong, TinhTrang) "
            //     + "VALUES ('{0}','{1}','{2}','{3}',{4})", p.MaPhong, p.TenPhong, p.SoDienThoai, p.MaLoaiPhong, p.TinhTrang);


            SqlConnection conn = connData.Connect1();
            conn.Open();
            string sql = "ThemMoiPhong";
            SqlCommand objCmd = new SqlCommand(sql, conn);
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.Parameters.Add("@MaPhong", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@TenPhong", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@SoDienThoai", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@MaLoaiPhong", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@TinhTrang", SqlDbType.Int);

            objCmd.Parameters["@MaPhong"].Value = p.MaPhong;
            objCmd.Parameters["@TenPhong"].Value = p.TenPhong;
            objCmd.Parameters["@SoDienThoai"].Value = p.SoDienThoai;
            objCmd.Parameters["@MaLoaiPhong"].Value = p.MaLoaiPhong;
            objCmd.Parameters["@TinhTrang"].Value = p.TinhTrang;
            objCmd.ExecuteNonQuery();
            conn.Close();
        }

        //
        public string NextID()
        {
            return Utilities.NextID(connData.GetLastID("Phong", "MaPhong"), "P");
        }

        public bool CheckExists(string p)
        {
            if (connData.CheckExistValue("Phong", "MaPhong", p))
            {
                return true;
            } return false;
        }

        //sửa
        public void SuaPhong(PhongBO p)
        {
            //string sql = string.Format("UPDATE Phong SET TenPhong = '{0}', SoDienThoai ='{1}', MaLoaiPhong='{2}', TinhTrang = '{3}' WHERE MaPhong = '{4}' ",
            //                         p.TenPhong, p.SoDienThoai, p.MaLoaiPhong, p.TinhTrang,p.MaPhong);
            //if (connData.ExeCuteQuery(sql))
            //{
            //    MessageBox.Show("sua nhan vien thanh cong", "thong bao", MessageBoxButtons.OK, MessageBoxIcon.Information);
            //}
            SqlConnection conn = connData.Connect1();
            conn.Open();
            string sql = "SuaPhong";
            SqlCommand objCmd = new SqlCommand(sql, conn);
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.Parameters.Add("@MaPhong", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@TenPhong", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@SoDienThoai", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@MaLoaiPhong", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@TinhTrang", SqlDbType.Int);

            objCmd.Parameters["@MaPhong"].Value = p.MaPhong;
            objCmd.Parameters["@TenPhong"].Value = p.TenPhong;
            objCmd.Parameters["@SoDienThoai"].Value = p.SoDienThoai;
            objCmd.Parameters["@MaLoaiPhong"].Value = p.MaLoaiPhong;
            objCmd.Parameters["@TinhTrang"].Value = p.TinhTrang;
            objCmd.ExecuteNonQuery();
            conn.Close();
        }

        //xóa
        public void Delete(string nv)
        {
            string sql = "Delete from NhanVien where MaNhanVien='" + nv + "'";
            connData.ExeCuteQuery(sql);
        }
    }
}
